﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Colorful.Furniture.Extensions;
using Colorful.Furniture.Wrappers;
using FreeSql;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using Volo.Abp;
using Volo.Abp.Application.Services;
using Volo.Abp.ObjectMapping;
using Colorful.Furniture.Func;
using Colorful.Furniture.PermissionManagement;
using Microsoft.AspNetCore.Authorization;
using Colorful.Furniture.ErpCustomerManagement;
using Colorful.Furniture.ErpOrderManagement;
using Colorful.Furniture.Enum;
using static SKIT.FlurlHttpClient.Wechat.Api.Models.ScanProductV2AddRequest.Types.Product.Types;
using Volo.Abp.Auditing;

namespace Colorful.Furniture.StatisticsManagement
{
    /// <summary>
    /// 统计信息服务
    /// </summary>
    [DisableAuditing]
    [Authorize]
    public class StatisticsAppService : ApplicationService
    {

        private readonly IBaseRepository<StatisticsSalesManAmount> _salesManAmountRepository;
        private readonly IFreeSql _freeSql;
        private readonly IObjectMapper _objectMapper;
        private readonly IDataPermissionFilter _dataPermissionFilter;

        public StatisticsAppService(
            IBaseRepository<StatisticsSalesManAmount> salesManAmountRepository,
            IFreeSql freeSql,
            IObjectMapper objectMapper,
            IDataPermissionFilter dataPermissionFilter
        )
        {
            _salesManAmountRepository = salesManAmountRepository;
            _freeSql = freeSql;
            _objectMapper = objectMapper;
            _dataPermissionFilter = dataPermissionFilter;
        }
        
        /// <summary>
        /// 可视化跑量统计表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PageResponseWrapper<List<FuncOrderCustomerOutputDto>> PostOrderCustomerPagedAsync(
            [FromBody] FuncOrderCustomerInputDto input)
        {
            //下单时间
            DateTime? addStartDate = input.AddDate?.Count == 2 ? Convert.ToDateTime(input.AddDate[0]) : null;
            DateTime? addEndDate = input.AddDate?.Count == 2 ? Convert.ToDateTime(input.AddDate[1]) : null;
            
            var query = _freeSql.Ado.Query<FuncOrderCustomerOutputDto>
                ($"execute proc_OrderCustomerStatistics @begin_time = '{addStartDate}', @end_time = '{addEndDate}', @customer_name = '{input.CustomerName}', @type = 1 ").ToList();

            var querySum = _freeSql.Ado.Query<FuncOrderCustomerOutputDto>
                ($"execute proc_OrderCustomerStatistics @begin_time = '{addStartDate}', @end_time = '{addEndDate}', @customer_name = '{input.CustomerName}', @type = 2 ").ToList();
            
            return new PageResponseWrapper<List<FuncOrderCustomerOutputDto>, object>
            {
                Total = 100,
                Items = query,
                SumItems = querySum
            };
        }



        /// <summary>
        /// 充值业务-每日销售额统计
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PageResponseWrapper<List<ProcFinanceRecordsSaleOutputDto>> PostFinanceRecordsSalePagedAsync(
            [FromBody] ProcFinanceRecordsSaleInputDto input)
        {
            //下单时间
            DateTime? addStartDate = input.AddDate?.Count == 2 ? Convert.ToDateTime(input.AddDate[0]) : null;
            DateTime? addEndDate = input.AddDate?.Count == 2 ? Convert.ToDateTime(input.AddDate[1]) : null;

            if (input.CustomerId.HasValue && input.ProductId.HasValue)
            {
                var query = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                ($"execute proc_cz_day_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id =  '{input.CustomerId}', @product_id = '{input.ProductId}' ").ToList();

                var querySum = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                    ($"execute proc_cz_day_sales_records_sum @begin_time = '{addStartDate}', @end_time = '{addEndDate}', @customer_id =  '{input.CustomerId}', @product_id = '{input.ProductId}' ").ToList();

                return new PageResponseWrapper<List<ProcFinanceRecordsSaleOutputDto>, object>
                {
                    Total = 100,
                    Items = query,
                    SumItems = querySum
                };
            }
            else if (!input.CustomerId.HasValue && input.ProductId.HasValue)
            {

                var query = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                ($"execute proc_cz_day_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id =  null, @product_id = '{input.ProductId}' ").ToList();

                var querySum = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                    ($"execute proc_cz_day_sales_records_sum @begin_time = '{addStartDate}', @end_time = '{addEndDate}', @customer_id =  null, @product_id = '{input.ProductId}' ").ToList();

                return new PageResponseWrapper<List<ProcFinanceRecordsSaleOutputDto>, object>
                {
                    Total = 100,
                    Items = query,
                    SumItems = querySum
                };
            }
            else if (input.CustomerId.HasValue && !input.ProductId.HasValue)
            {

                var query = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                ($"execute proc_cz_day_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id = '{input.CustomerId}' , @product_id = null ").ToList();

                var querySum = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                    ($"execute proc_cz_day_sales_records_sum @begin_time = '{addStartDate}', @end_time = '{addEndDate}', @customer_id = '{input.CustomerId}' , @product_id = null ").ToList();

                return new PageResponseWrapper<List<ProcFinanceRecordsSaleOutputDto>, object>
                {
                    Total = 100,
                    Items = query,
                    SumItems = querySum
                };
            }
            else
            {
                var query = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                ($"execute proc_cz_day_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id =  null, @product_id = null ").ToList();

                var querySum = _freeSql.Ado.Query<ProcFinanceRecordsSaleOutputDto>
                    ($"execute proc_cz_day_sales_records_sum @begin_time = '{addStartDate}', @end_time = '{addEndDate}', @customer_id =  null, @product_id = null ").ToList();

                return new PageResponseWrapper<List<ProcFinanceRecordsSaleOutputDto>, object>
                {
                    Total = 100,
                    Items = query,
                    SumItems = querySum
                };
            }

            
        }

        

        /// <summary>
        ///  导出-客户对账单列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [RemoteService(IsEnabled = false)]
        public async Task<List<ProcClRecordsSaleOutputDto>> PostExportClCustomerRecordsSaleListAsync(ProcClRecordsSaleInputDto input)
        {
            //下单时间
            DateTime? addStartDate = input.AddDate?.Count == 2 ? Convert.ToDateTime(input.AddDate[0]) : null;
            DateTime? addEndDate = input.AddDate?.Count == 2 ? Convert.ToDateTime(input.AddDate[1]) : null;

            if (input.CustomerId.HasValue && input.ProductId.HasValue)
            {
                var query = _freeSql.Ado.Query<ProcClRecordsSaleOutputDto>
                ($"execute proc_cl_customer_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id =  '{input.CustomerId}', @product_id = '{input.ProductId}' ").ToList();

                query.ForEach(x => x.DayTime = $"{addStartDate.ToDateTime().ToString("yyyy-MM-dd")} 至 {addEndDate.ToDateTime().ToString("yyyy-MM-dd")}");
                
                return query;
            }

            else if (!input.CustomerId.HasValue && input.ProductId.HasValue)
            {

                var query = _freeSql.Ado.Query<ProcClRecordsSaleOutputDto>
                ($"execute proc_cl_customer_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id =  null, @product_id = '{input.ProductId}' ").ToList();

                query.ForEach(x => x.DayTime = $"{addStartDate.ToDateTime().ToString("yyyy-MM-dd")} 至 {addEndDate.ToDateTime().ToString("yyyy-MM-dd")}");

                return query;
            }
            else if (input.CustomerId.HasValue && !input.ProductId.HasValue)
            {

                var query = _freeSql.Ado.Query<ProcClRecordsSaleOutputDto>
                ($"execute proc_cl_customer_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id = '{input.CustomerId}' , @product_id = null ").ToList();

                query.ForEach(x => x.DayTime = $"{addStartDate.ToDateTime().ToString("yyyy-MM-dd")} 至 {addEndDate.ToDateTime().ToString("yyyy-MM-dd")}");

                return query;
            }
            else
            {
                var query = _freeSql.Ado.Query<ProcClRecordsSaleOutputDto>
                ($"execute proc_cl_customer_sales_records @begin_time = '{addStartDate}', @end_time = '{addEndDate}', " +
                 $"@customer_id =  null, @product_id = null ").ToList();

                query.ForEach(x => x.DayTime = $"{addStartDate.ToDateTime().ToString("yyyy-MM-dd")} 至 {addEndDate.ToDateTime().ToString("yyyy-MM-dd")}");

                return query;
            }
            
        }


        /// <summary>
        /// 导出-客户对账单
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [DisableAuditing]
        public async Task<IActionResult> PostExportClCustomerRecordsSaleAsync([FromBody] ProcClRecordsSaleInputDto input)
        {
            //try
            //{
            var exportList = await PostExportClCustomerRecordsSaleListAsync(input);

            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("客户对账单导出");

            var x = 1;
            var y = 1;

            var columnTitles = new List<string>
        {
            "渠道号","结算周期","业务名称","客户名称","结算单价","结算单数", "结算总金额"
        };

            var columnIndex = 0;
            foreach (var columnTitle in columnTitles)
            {
                // 设置标题样式
                var cell = worksheet.Cells[x, y++];
                cell.Style.Font.Bold = true;
                worksheet.Row(1).Height = 30;//设置行高
                worksheet.Column(columnIndex + 1).Width = 4 * columnTitle.Length;//设置列宽
                cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cell.Value = columnTitle;
                columnIndex++;
            }

            worksheet.Column(1).Width = 16;
            worksheet.Column(2).Width = 30;
            worksheet.Column(3).Width = 30;
            worksheet.Column(4).Width = 30;
            worksheet.Column(7).Width = 25;

            foreach (var item in exportList)
            {
                x++;
                worksheet.Row(x).Height = 26;//设置行高

                var cell = worksheet.Cells[x, 1];
                cell.Value = item.ProductNo;

                cell = worksheet.Cells[x, 2];
                cell.Value = item.DayTime;

                cell = worksheet.Cells[x, 3];
                cell.Value = item.ProductName;

                cell = worksheet.Cells[x, 4];
                cell.Value = item.CustomerName;

                cell = worksheet.Cells[x, 5];
                cell.Value = item.Price;

                cell = worksheet.Cells[x, 6];
                cell.Value = item.AllNumber;

                cell = worksheet.Cells[x, 7];
                cell.Value = item.TotalPrice;


            }
            using var mem = new MemoryStream();
            package.SaveAs(mem);
            var bytes = await Task.Run(() => mem.ToArray());
            return new FileContentResult(bytes, "application/xlsx")
            {
                FileDownloadName = "客户对账单导出.xlsx"
            };
            //}
            //catch (Exception)
            //{
            //    throw new UserFriendlyException("采购明细单导出数据异常，请联系管理员");
            //}
        }

        

    }


}



